﻿using System;
using System.Data;
using System.Data.Common;
using System.Text.RegularExpressions;
using System.Web.Security;
using System.Web;

public struct DetalhesCategoria
{
    public int CategoriaID;
    public string Nome;
}

public struct DetalhesProduto
{
    public int ProductID;
    public string Nome;
    public string Descricao;
    public decimal Preco;
    public string Imagem1;
    public string Imagem2;
    public string Imagem3;
    public string Tb_Imagem1;
    public bool PromoFront;
    public bool PromoCat;
    public int EmpresaID;
    public string NomeEmpresa;
}

public struct DetalhesEmpresa
{
    public int EmpresaID;
    public string Nome;
    public string Descricao;
    public string Endereco;
    public string Numero;
    public string Telefone;
    public string Orkut;
    public string Msn;
    public string Logotipo;
    public string Cep;
    public string Facebook;
    public string Bairro;
    public string Twitter;
    public string Paginaweb;
    public string Email;
    public string Celular;
    public string Youtube;
    public string Blog;
    public string CategoriaID;
    public int BairroID;
}

/// <summary>
/// Product catalog business tier component
/// </summary>
public static class CatalogAccess
{
    static CatalogAccess()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    // Retrieve the list of departments 
    public static DataTable GetCategorias()
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "GetCategorias";
        // execute the stored procedure and return the results
        return GenericDataAccess.ExecuteSelectCommand(comm);
    }

    // Retrieve the list of departments 
    public static DataTable GetCheckHomeAndCat(string codPublicacao)
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "CatalogGetCheckHomeAndCat";

        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@CodPublicacao";
        param.Value = codPublicacao;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);

        // execute the stored procedure and return the results
        return GenericDataAccess.ExecuteSelectCommand(comm);
    }

    // Retrieve the list of departments 
    public static DataTable GetBairros()
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "GetBairros";
        // execute the stored procedure and return the results
        return GenericDataAccess.ExecuteSelectCommand(comm);
    }

    // Retrieve the list of departments 
    public static DataTable GetMenuHorizontal()
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "CatalogGetMenuHorizontal";
        // execute the stored procedure and return the results
        return GenericDataAccess.ExecuteSelectCommand(comm);
    }

    // Retrieve the list of departments 
    public static DataTable GetFileNameLogotipo(String empresaId)
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "CatalogReturnFilenameLogotipo";
        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@EmpresaID";
        param.Value = empresaId;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);

        // execute the stored procedure and return the results
        return GenericDataAccess.ExecuteSelectCommand(comm);
    }

    // Get product details
    public static DetalhesCategoria GetDetalhesCategoria(string categoriaId)
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "CatalogGetDetalhesCategoria";
        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@CategoriaID";
        param.Value = categoriaId;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);

        // execute the stored procedure
        DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
        // wrap retrieved data into a ProductDetails object
        DetalhesCategoria details = new DetalhesCategoria();
        if (table.Rows.Count > 0)
        {
            // get the first table row
            DataRow dr = table.Rows[0];
            // get product details
            details.CategoriaID = int.Parse(categoriaId);
            details.Nome = dr["nome"].ToString();
        }
        // return department details
        return details;
    }

    // Get product details
    public static DetalhesProduto GetDetalhesProduto(string productId)
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "CatalogGetDetalhesProduto";
        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@ProductID";
        param.Value = productId;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);

        // execute the stored procedure
        DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
        // wrap retrieved data into a ProductDetails object
        DetalhesProduto details = new DetalhesProduto();
        if (table.Rows.Count > 0)
        {
            // get the first table row
            DataRow dr = table.Rows[0];
            // get product details
            details.ProductID = int.Parse(productId);
            details.Nome = dr["nome"].ToString();
            details.Descricao = dr["descricao"].ToString();
            details.Preco = Math.Round(Decimal.Parse(dr["price"].ToString()), 2);
            details.Imagem1 = dr["imagem1"].ToString();
            details.Imagem2 = dr["imagem2"].ToString();
            details.Imagem3 = dr["imagem3"].ToString();
            details.PromoFront = bool.Parse(dr["PromoFront"].ToString());
            details.PromoCat = bool.Parse(dr["PromoCat"].ToString());
            details.NomeEmpresa = dr["nomeEmpresa"].ToString();
            details.EmpresaID = int.Parse(dr["empresaID"].ToString());
        }
        // return department details
        return details;
    }

    // Get product details
    public static DetalhesEmpresa GetDetalhesEmpresa(string empresaId)
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "CatalogGetEmpresa";
        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@EmpresaID";
        param.Value = empresaId;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);

        // execute the stored procedure
        DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
        // wrap retrieved data into a ProductDetails object
        DetalhesEmpresa details = new DetalhesEmpresa();
        if (table.Rows.Count > 0)
        {
            // get the first table row
            DataRow dr = table.Rows[0];
            // get product details
            details.EmpresaID = int.Parse(empresaId);
            details.Nome = dr["nome"].ToString();
            details.Descricao = dr["descricao"].ToString();
            details.Bairro = dr["bairro"].ToString();
            details.Endereco = dr["endereco"].ToString();
            details.Orkut = dr["orkut"].ToString();
            details.Facebook = dr["facebook"].ToString();
            details.Msn = dr["msn"].ToString();
            details.Twitter = dr["twitter"].ToString();
            details.Numero = dr["numero"].ToString();
            details.Telefone = dr["telefone"].ToString();
            details.Logotipo = dr["logotipo"].ToString();
            details.Paginaweb = dr["paginaweb"].ToString();
            details.Cep = dr["cep"].ToString();
            details.Email = dr["email"].ToString();
            details.Celular = dr["celular"].ToString();
            details.Blog = dr["blog"].ToString();
            details.Youtube = dr["youtube"].ToString();
            details.CategoriaID = dr["categoriaID"].ToString();
            details.BairroID = int.Parse(dr["bairroID"].ToString());
        }
        // return department details
        return details;
    }

    // retrieve the list of companies in a category
    public static DataTable GetEmpresasNaCategoria(string categoryId, string pageNumber, out int howManyPages)
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "CatalogGetEmpresasCategoria";
        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@CategoryID";
        param.Value = categoryId;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@DescriptionLength";
        param.Value = AcheiCampinasConfiguration.EmpresaDescriptionLength;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@PageNumber";

        param.Value = pageNumber;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@EmpresasPerPage";
        param.Value = AcheiCampinasConfiguration.EmpresasPerPage;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@HowManyEmpresas";
        param.Direction = ParameterDirection.Output;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        // execute the stored procedure and save the results in a DataTable
        DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
        // calculate how many pages of products and set the out parameter
        int howManyEmpresas = Int32.Parse(comm.Parameters["@HowManyEmpresas"].Value.ToString());
        howManyPages = (int)Math.Ceiling((double)howManyEmpresas /(double)AcheiCampinasConfiguration.EmpresasPerPage);
        // return the page of products
        return table;
    }

    // retrieve the list of products in a category
    public static DataTable GetProdutosDaEmpresa(string empresaId, string pageNumber, out int howManyPages)
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "CatalogGetProdutosDaEmpresa";
        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@EmpresaID";
        param.Value = empresaId;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@DescriptionLength";
        param.Value = AcheiCampinasConfiguration.ProductDescriptionLength;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@PageNumber";

        param.Value = pageNumber;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@ProductsPerPage";
        param.Value = AcheiCampinasConfiguration.ProductsPerPageDaEmpresa;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@HowManyProducts";
        param.Direction = ParameterDirection.Output;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        // execute the stored procedure and save the results in a DataTable
        DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
        // calculate how many pages of products and set the out parameter
        int howManyProducts = Int32.Parse(comm.Parameters["@HowManyProducts"].Value.ToString());
        howManyPages = (int)Math.Ceiling((double)howManyProducts / (double)AcheiCampinasConfiguration.ProductsPerPageDaEmpresa);
        // return the page of products
        return table;
    }

    // Retrieve the list of companies on catalog promotion
    public static DataTable GetEmpresasEmFrontPromo(string pageNumber, out int howManyPages)
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "CatalogGetEmpresasPromotion";
        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@DescriptionLength";
        param.Value = AcheiCampinasConfiguration.EmpresaDescriptionLength;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@PageNumber";
        param.Value = pageNumber;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@EmpresasPerPage";
        param.Value = AcheiCampinasConfiguration.EmpresasPerPage;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@HowManyEmpresas";
        param.Direction = ParameterDirection.Output;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);

        // execute the stored procedure and save the results in a DataTable
        DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
        // calculate how many pages of products and set the out parameter
        int howManyEmpresas = Int32.Parse(comm.Parameters["@HowManyEmpresas"].Value.ToString());
        howManyPages = (int)Math.Ceiling((double)howManyEmpresas / (double)AcheiCampinasConfiguration.EmpresasPerPage);
        // return the page of products
        return table;
    }

    public static DataTable GetAnunciosEmFrontPromo()
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "CatalogGetAnunciosEmFrontPromo";
        // execute the stored procedure and return the results
        return GenericDataAccess.ExecuteSelectCommand(comm);
    }

    public static DataTable GetAnunciosNaCategoria(string categoryId)
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "CatalogGetAnunciosNaCategoria";
        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@CategoryID";
        param.Value = categoryId;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        // execute the stored procedure and return the results
        return GenericDataAccess.ExecuteSelectCommand(comm);
    }

    public static DataTable GetEmpresasCliente()
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "CatalogGetEmpresasCliente";
        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@ClienteID";
        param.Value = Membership.GetUser(HttpContext.Current.User.Identity.Name).ProviderUserKey;
        param.DbType = DbType.Guid;
        param.Size = 16;
        comm.Parameters.Add(param);
        // execute the stored procedure and return the results
        return GenericDataAccess.ExecuteSelectCommand(comm);
    }

    public static DataTable GetEmpresasClienteMenor10()
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "CatalogGetEmpresasClienteMenor10";
        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@ClienteID";
        param.Value = Membership.GetUser(HttpContext.Current.User.Identity.Name).ProviderUserKey;
        param.DbType = DbType.Guid;
        param.Size = 16;
        comm.Parameters.Add(param);
        // execute the stored procedure and return the results
        return GenericDataAccess.ExecuteSelectCommand(comm);
    }

    // Search the product catalog
    public static DataTable SearchProdutos(string searchString, string allWords, string pageNumber, out int howManyPages)
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "SearchProdutos";
        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@DescriptionLength";
        param.Value = AcheiCampinasConfiguration.ProductDescriptionLength;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@AllWords";
        param.Value = allWords.ToUpper() == "TRUE" ? "1" : "0";
        param.DbType = DbType.Byte;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@PageNumber";
        param.Value = pageNumber;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@ProductsPerPage";
        param.Value = AcheiCampinasConfiguration.ProductsPerPageResults;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@HowManyResults";
        param.Direction = ParameterDirection.Output;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);

        // define the maximum number of words
        int howManyWords = 5;
        // transform search string into array of words
        string[] words = Regex.Split(searchString, "[^a-zA-Z0-9]+");

        // add the words as stored procedure parameters
        int index = 1;
        for (int i = 0; i <= words.GetUpperBound(0) && index <= howManyWords; i++)
            // ignore short words
            if (words[i].Length > 2)
            {
                // create the @Word parameters
                param = comm.CreateParameter();
                param.ParameterName = "@Word" + index.ToString();
                param.Value = words[i];
                param.DbType = DbType.String;
                comm.Parameters.Add(param);
                index++;
            }

        // execute the stored procedure and save the results in a DataTable
        DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
        // calculate how many pages of products and set the out parameter
        int howManyProducts = Int32.Parse(comm.Parameters["@HowManyResults"].Value.ToString());
        howManyPages = (int)Math.Ceiling((double)howManyProducts / (double)AcheiCampinasConfiguration.ProductsPerPageResults);
        // return the page of products
        return table;
    }

    // Search the product catalog
    public static DataTable SearchEmpresas(string searchString, string allWords, string pageNumber, out int howManyPages)
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "SearchEmpresas";
        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@DescriptionLength";
        param.Value = AcheiCampinasConfiguration.ProductDescriptionLength;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@AllWords";
        param.Value = allWords.ToUpper() == "TRUE" ? "1" : "0";
        param.DbType = DbType.Byte;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@PageNumber";
        param.Value = pageNumber;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@EmpresasPerPage";
        param.Value = AcheiCampinasConfiguration.EmpresasPerPageResults;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@HowManyResults";
        param.Direction = ParameterDirection.Output;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);

        // define the maximum number of words
        int howManyWords = 5;
        // transform search string into array of words
        string[] words = Regex.Split(searchString, "[^a-zA-Z0-9]+");

        // add the words as stored procedure parameters
        int index = 1;
        for (int i = 0; i <= words.GetUpperBound(0) && index <= howManyWords; i++)
            // ignore short words
            if (words[i].Length > 2)
            {
                // create the @Word parameters
                param = comm.CreateParameter();
                param.ParameterName = "@Word" + index.ToString();
                param.Value = words[i];
                param.DbType = DbType.String;
                comm.Parameters.Add(param);
                index++;
            }

        // execute the stored procedure and save the results in a DataTable
        DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
        // calculate how many pages of products and set the out parameter
        int howManyProducts = Int32.Parse(comm.Parameters["@HowManyResults"].Value.ToString());
        howManyPages = (int)Math.Ceiling((double)howManyProducts / (double)AcheiCampinasConfiguration.EmpresasPerPageResults);
        // return the page of products
        return table;
    }

    // retrieve the list of products in a category
    public static DataTable GetAllProductsEmpresa(string empresaId)
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "CatalogGetAllProductsEmpresa";
        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@EmpresaID";
        param.Value = empresaId;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        // execute the stored procedure and save the results in a DataTable
        DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
        return table;
    }

    // retrieve the list of products in a category
    public static DataTable GetInformacoesEmpresa(string empresaId)
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "CatalogGetInformacoesEmpresa";
        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@EmpresaID";
        param.Value = empresaId;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        // execute the stored procedure and save the results in a DataTable
        DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
        return table;
    }

    // Create a new product
    public static bool CreateProduct(string categoryId, string empresaId, string name, string description, string price, string image1, string image2, string image3, string tb_image1,string PromoCat, string PromoFront)
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "CatalogCreateProduct";
        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@CategoryID";
        param.Value = categoryId;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@EmpresaID";
        param.Value = empresaId;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@ProductName";
        param.Value = name;
        param.DbType = DbType.String;
        param.Size = 100;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@ProductDescription";
        param.Value = description;
        param.DbType = DbType.String;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Price";
        param.Value = price;
        param.DbType = DbType.Decimal;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Imagem1";
        param.Value = image1;
        param.DbType = DbType.String;
        param.Size = 80;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Imagem2";
        param.Value = image2;
        param.DbType = DbType.String;
        param.Size = 80;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Imagem3";
        param.Value = image3;
        param.DbType = DbType.String;
        param.Size = 80;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Tb_Imagem1";
        param.Value = tb_image1;
        param.DbType = DbType.String;
        param.Size = 80;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@PromoCat";
        param.Value = true;
        param.DbType = DbType.Boolean;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@PromoFront";
        param.Value = true;
        param.DbType = DbType.Boolean;
        comm.Parameters.Add(param);
        // result will represent the number of changed rows
        int result = -1;
        try
        {
            // execute the stored procedure
            result = GenericDataAccess.ExecuteNonQuery(comm);
        }
        catch
        {
            // any errors are logged in GenericDataAccess, we ignore them here
        }
        // result will be 1 in case of success 
        return (result >= 1);
    }

    // Update an existing product
    public static bool UpdateProduct(string productId, string name, string description, string price)
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "CatalogUpdateProduct";
        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@ProductID";
        param.Value = productId;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@ProductName";
        param.Value = name;
        param.DbType = DbType.String;
        param.Size = 100;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@ProductDescription";
        param.Value = description;
        param.DbType = DbType.String;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Price";
        param.Value = price;
        param.DbType = DbType.Decimal;
        comm.Parameters.Add(param);
 
        // result will represent the number of changed rows
        int result = -1;
        try
        {
            // execute the stored procedure
            result = GenericDataAccess.ExecuteNonQuery(comm);
        }
        catch
        {
            // any errors are logged in GenericDataAccess, we ignore them here
        }
        // result will be 1 in case of success 
        return (result != -1);
    }

    public static bool UpdateAnunciante(DetalhesEmpresa empresa)
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "CatalogEmpresaUpdate";
        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@NomeEmpresa";
        param.Value = empresa.Nome;
        param.DbType = DbType.String;
        param.Size = 80;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@EmpresaID";
        param.Value = empresa.EmpresaID;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@DescricaoEmpresa";
        param.Value = empresa.Descricao;
        param.DbType = DbType.String;
        param.Size = 500;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Endereco";
        param.Value = empresa.Endereco;
        param.DbType = DbType.String;
        param.Size = 80;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Numero";
        param.Value = empresa.Numero;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Bairro";
        param.Value = empresa.Bairro;
        param.DbType = DbType.String;
        param.Size = 80;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Cep";
        param.Value = empresa.Cep;
        param.DbType = DbType.String;
        param.Size = 10;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Telefone";
        param.Value = empresa.Telefone;
        param.DbType = DbType.String;
        param.Size = 10;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Celular";
        param.Value = empresa.Celular;
        param.DbType = DbType.String;
        param.Size = 10;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@PaginaWeb";
        param.Value = empresa.Paginaweb;
        param.DbType = DbType.String;
        param.Size = 80;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Email";
        param.Value = empresa.Email;
        param.DbType = DbType.String;
        param.Size = 50;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Msn";
        param.Value = empresa.Msn;
        param.DbType = DbType.String;
        param.Size = 80;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Twitter";
        param.Value = empresa.Twitter;
        param.DbType = DbType.String;
        param.Size = 80;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Facebook";
        param.Value = empresa.Facebook;
        param.DbType = DbType.String;
        param.Size = 80;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Orkut";
        param.Value = empresa.Orkut;
        param.DbType = DbType.String;
        param.Size = 80;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Youtube";
        param.Value = empresa.Youtube;
        param.DbType = DbType.String;
        param.Size = 80;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Blog";
        param.Value = empresa.Blog;
        param.DbType = DbType.String;
        param.Size = 80;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@CategoriaID";
        param.Value = empresa.CategoriaID;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@BairroID";
        param.Value = empresa.BairroID;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);

        // result will represent the number of changed rows
        int result = -1;
        try
        {
            // execute the stored procedure
            result = GenericDataAccess.ExecuteNonQuery(comm);

        }
        catch
        {
            // any errors are logged in GenericDataAccess, we ignore them here
        }

        // result will be 1 in case of success
        int teste = result;

        return (result >= 1);
    }

    // Create a new product
    public static bool CriarAnunciante(String nomeEmpresa, String descricaoEmpresa, String endereco,
            String numero, String bairro, String cep, String telefone, String celular, String paginaweb, String email, String msn, String twitter, 
            String facebook, String orkut, String blog, String youtube, String fileName1, String categoriaId, String bairroId)
    {

        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "CatalogCriarAnunciante";
        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@NomeEmpresa";
        param.Value = nomeEmpresa;
        param.DbType = DbType.String;
        param.Size = 80;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@DescricaoEmpresa";
        param.Value = descricaoEmpresa;
        param.DbType = DbType.String;
        param.Size = 500;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Endereco";
        param.Value = endereco;
        param.DbType = DbType.String;
        param.Size = 80;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Numero";
        param.Value = numero;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Bairro";
        param.Value = bairro;
        param.DbType = DbType.String;
        param.Size = 80;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Cep";
        param.Value = cep;
        param.DbType = DbType.String;
        param.Size = 10;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Telefone";
        param.Value = telefone;
        param.DbType = DbType.String;
        param.Size = 10;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Celular";
        param.Value = celular;
        param.DbType = DbType.String;
        param.Size = 10;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@PaginaWeb";
        param.Value = paginaweb;
        param.DbType = DbType.String;
        param.Size = 80;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Email";
        param.Value = email;
        param.DbType = DbType.String;
        param.Size = 50;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Msn";
        param.Value = msn;
        param.DbType = DbType.String;
        param.Size = 80;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Twitter";
        param.Value = twitter;
        param.DbType = DbType.String;
        param.Size = 80;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Facebook";
        param.Value = facebook;
        param.DbType = DbType.String;
        param.Size = 80;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Orkut";
        param.Value = orkut;
        param.DbType = DbType.String;
        param.Size = 80;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Youtube";
        param.Value = youtube;
        param.DbType = DbType.String;
        param.Size = 80;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Blog";
        param.Value = blog;
        param.DbType = DbType.String;
        param.Size = 80;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Logotipo";
        param.Value = fileName1;
        param.DbType = DbType.String;
        param.Size = 80;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@AnuncianteID";
        param.Value = Membership.GetUser(HttpContext.Current.User.Identity.Name).ProviderUserKey;
        param.DbType = DbType.Guid;
        param.Size = 16;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@CategoriaID";
        param.Value = categoriaId;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@BairroID";
        param.Value = bairroId;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);

        // result will represent the number of changed rows
        int result = -1;
        try
        {
            // execute the stored procedure
            result = GenericDataAccess.ExecuteNonQuery(comm);
            
        }
        catch
        {
            // any errors are logged in GenericDataAccess, we ignore them here
        }

        // result will be 1 in case of success 
        return (result >= 1);
    }


    // Create a new product
    public static string addPublicacoes(String EmpresaId, bool frontLogo, bool catLogo, string categoriaId, string banner)
    {

        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "CatalogAddPublicacoes";
        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param = comm.CreateParameter();
        param.ParameterName = "@EmpresaID";
        param.Value = EmpresaId;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@CategoriaPage";
        param.Value = catLogo;
        param.DbType = DbType.Boolean;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@FrontPage";
        param.Value = frontLogo;
        param.DbType = DbType.Boolean;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Banner";
        param.Value = banner;
        param.DbType = DbType.String;
        param.Size = 200;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@CategoriaID";
        param.Value = categoriaId;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);

            // execute the stored procedure
            return GenericDataAccess.ExecuteScalar(comm);
    }


    // Update an existing product
    public static bool UpdateProductImage(string productId, string image, string pos)
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "CatalogUpdateProductImage";
        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@ProductID";
        param.Value = productId;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Imagem";
        param.Value = image;
        param.DbType = DbType.String;
        param.Size = 80;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Pos";
        param.Value = pos;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);

        // result will represent the number of changed rows
        int result = -1;
        try
        {
            // execute the stored procedure
            result = GenericDataAccess.ExecuteNonQuery(comm);
        }
        catch
        {
            // any errors are logged in GenericDataAccess, we ignore them here
        }
        // result will be 1 in case of success 
        return (result != -1);
    }

    // Update an existing product
    public static bool UpdateImagemAnunciante(String empresaId, String filename)
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "CatalogUpdateImagemAnunciante";
        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@EmpresaID";
        param.Value = empresaId;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Logotipo";
        param.Value = filename;
        param.DbType = DbType.String;
        param.Size = 80;
        comm.Parameters.Add(param);

        // result will represent the number of changed rows
        int result = -1;
        try
        {
            // execute the stored procedure
            result = GenericDataAccess.ExecuteNonQuery(comm);
        }
        catch
        {
            // any errors are logged in GenericDataAccess, we ignore them here
        }
        // result will be 1 in case of success 
        return (result != -1);
    }

    // get categories that do not contain a specified product
    public static DataTable GetCategoriasSemEsteProduto(string produtoId)
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "CatalogGetCategoriasSemEsteProduto";
        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@ProductID";
        param.Value = produtoId;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        // execute the stored procedure
        return GenericDataAccess.ExecuteSelectCommand(comm);
    }

    // move product to a new category
    public static bool MoveProductToCategory(string produtoId, string newCategoryId)
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "CatalogMoverProdutoCategoria";
        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@ProductID";

        param.Value = produtoId;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@NewCategoryID";
        param.Value = newCategoryId;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        // result will represent the number of changed rows
        int result = -1;
        try
        {
            // execute the stored procedure
            result = GenericDataAccess.ExecuteNonQuery(comm);
        }
        catch
        {
            // any errors are logged in GenericDataAccess, we ignore them here
        }
        // result will be 1 in case of success 
        return (result != -1);
    }

    // deletes a product from the product catalog
    public static bool DeleteProduct(string produtoId)
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "CatalogDeleteProduto";
        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@ProductID";
        param.Value = produtoId;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        // result will represent the number of changed rows
        int result = -1;
        try
        {
            // execute the stored procedure
            result = GenericDataAccess.ExecuteNonQuery(comm);
        }
        catch
        {
            // any errors are logged in GenericDataAccess, we ignore them here
        }
        // result will be 1 in case of success 
        return (result != -1);
    }

    // deletes a product from the product catalog
    public static bool DeleteEmpresa(string empresaId)
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "CatalogDeleteEmpresa";
        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@EmpresaID";
        param.Value = empresaId;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        // result will represent the number of changed rows
        int result = -1;
        try
        {
            // execute the stored procedure
            result = GenericDataAccess.ExecuteNonQuery(comm);
        }
        catch
        {
            // any errors are logged in GenericDataAccess, we ignore them here
        }
        // result will be 1 in case of success 
        return (result != -1);
    }

    // Gets the reviews for a specific product
    public static DataTable GetComentarios(string produtoId)
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "CatalogGetComentarios";
        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@ProdutoID";
        param.Value = produtoId;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        // execute the stored procedure
        return GenericDataAccess.ExecuteSelectCommand(comm);
    }

    public static bool AddComentario(string produtoId, string comentario, string NomeComentarista)
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "CatalogAddComentario";
        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param = comm.CreateParameter();
        param.ParameterName = "@ProdutoID";
        param.Value = produtoId;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Comentario";
        param.Value = comentario;
        param.DbType = DbType.String;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@NomeComentarista";
        param.Value = NomeComentarista;
        param.DbType = DbType.String;
        param.Size = 80;
        comm.Parameters.Add(param);
        // returns true in case of success or false in case of an error
        try
        {// execute the stored procedure and return true if it executes
         // successfully, or false otherwise
            return (GenericDataAccess.ExecuteNonQuery(comm) != -1);
        }
        catch
        {
            // prevent the exception from propagating, but return false to
            // signal the error
            return false;
        }
    }

    public static bool updateStatusAndShow(string codPublicacao, string status, Boolean promoCat, Boolean promoFront)
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "CatalogUpdateStatusAndShow";
        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param = comm.CreateParameter();
        param.ParameterName = "@CodPublicacaoID";
        param.Value = codPublicacao;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Status";
        param.Value = status;
        param.DbType = DbType.String;
        param.Size = 20;
        comm.Parameters.Add(param);

        param = comm.CreateParameter();
        param.ParameterName = "@PromoCat";
        param.Value = promoCat;
        param.DbType = DbType.Boolean;
        comm.Parameters.Add(param);

        param = comm.CreateParameter();
        param.ParameterName = "@PromoFront";
        param.Value = promoFront;
        param.DbType = DbType.Boolean;
        comm.Parameters.Add(param);

        // returns true in case of success or false in case of an error
        try
        {// execute the stored procedure and return true if it executes
            // successfully, or false otherwise
            return (GenericDataAccess.ExecuteNonQuery(comm) != -1);
        }
        catch
        {
            // prevent the exception from propagating, but return false to
            // signal the error
            return false;
        }
    }

    public static bool updateStatusPublicacao(string codPublicacao, string status)
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "CatalogUpdateStatusPublicacao";
        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param = comm.CreateParameter();
        param.ParameterName = "@CodPublicacaoID";
        param.Value = codPublicacao;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@Status";
        param.Value = status;
        param.DbType = DbType.String;
        param.Size = 20;
        comm.Parameters.Add(param);
        // returns true in case of success or false in case of an error
        try
        {// execute the stored procedure and return true if it executes
            // successfully, or false otherwise
            return (GenericDataAccess.ExecuteNonQuery(comm) != -1);
        }
        catch
        {
            // prevent the exception from propagating, but return false to
            // signal the error
            return false;
        }
    }
}
